Release 10.1A: OpenEdge Development:
Progress Dynamics Basic Development

Indexing guidelines

When designing a database, there are indexing guidelines that you should consider. First, no table should be defined without at least one index. This is generally a good policy. Not observing this causes many performance problems. However, Progress Dynamics has particular problems dealing with tables that have no index.

Try not to make a key that has a real meaning the only unique identifier on a table. There should be a key whose values cannot be forced to change. Any name or number whose value is not a completely arbitrary and never-changing sequence value might be a problematic choice for a key value. Certainly, a value that has a good probability of changing is a poor candidate for a key.

For example, textbooks on relational theory and SQL (not to mention Progress) routinely use examples where a meaningful key value is used as the sole join field for two tables, such as Customer.SalesRep and SalesRep.SalesRep. The sales rep’s initials are stored as the value for these fields. However, this definition is inherently denormalized because the sales rep’s initials are stored in two different tables and might change (as the result of marriage, for instance).

Every table should have a unique identifier. That unique identifier should be an unchanging sequence value that has no external meaning at all. All relationships to the table are then based on this unique identifier. Having alternate keys that are multi-component is fine, as long as there is another “primary” way of relating entities to one another.

Progress Dynamics supports a specific mechanism for defining keys called Object IDs, which are discussed in the "Object IDs and site numbers in Progress Dynamics" section.

Cascading data items (such as the SalesRep initials) down from parent to child is sometimes necessary, such as for performance reasons, but this is really a form of denormalization and should be avoided.You might want to provide a meaningful piece of information, the ID of the sales rep in this example, in the child table to avoid having to retrieve the associated parent record. However, putting the key there might not accomplish what you want. In this example, you might want to display the actual sales rep’s name, rather than initials, when displaying a Customer record. To get the name, you have to join to the SalesRep table anyway. If you use an arbitrary numeric sequence number to join the two tables, you avoid writing code for cascading changes.

Other points to consider about indexes and defining foreign key relationships are:

Always put an index on a foreign key field that involves only that field, Customer.SalesRep, or more properly, Customer.SalesRepSequence in the example. This allows you to efficiently determine relationships, such as Customers for a SalesRep, at the start of the index at the least.
Never use the RECID or ROWID of a record to relate records. RECID values and ROWID values are not preserved across database dumps and reloads. In fact, with support for multiple storage areas, they are no longer unique across the database.